动态SQL

PL/SQL执行SQL语句时，有的SQL语句只能在运行阶段才能建立，例如当查询条件为用户输入时，SQL引擎无法在编译期对该语句进行确定，只有在用户输入一定的查询条件才能提交给SQL引擎进行处理，这种方式被称作动态SQL语句。

动态SQL是在运行时产生和执行SQL语句的一种编程方法。下列情况下动态SQL是非常有用的。

像编写ad hoc查询系统一样，编写一个多用途和灵活性非常强的应用程序。
编写必须用DDL语句的应用程序。
在编译时并不知道完整的SQL语句。
在编译时并不知道输入输出变量的类型和数量。

可以用EXECUTE IMMEDIATE语句处理大多数的动态SQL语句，EXECUTE IMMEDIATE语句最多返回一行结果集。

如果动态SQL语句返回结果集为多行，使用OPEN FOR, FETCH和CLOSE语句。

使用EXECUTE IMMEDIATE

EXECUTE IMMEDIATE支持DDL、DCL、DML以及单行SELECT语句。

SELECT语句如果返回单行，结果集放在 INTO 子句中；如果返回多行，结果集放在 BULK COLLECT INTO 子句中，当结果集返回多行时需要用集合类型（嵌套表，可变数组）接收。

如果动态SQL语句包含绑定变量占位符，则每个占位符必须在EXECTURE IMMEDIATE语句的USING子句有对应的绑定变量。

用USING 定义输入变量，USING子句不能使用NULL作为变量。为了解决此限制，在需要使用NULL的地方使用无初始化的变量来替代。USING子句不支持select语句。不要有SQL引擎不支持类型的绑定变量。

语法：

EXECUTE IMMEDIATE    ---动态SQL的起始标示，表示是动态SQL调用
SQL 子句               ---动态SQL语句
                        方式1：提前声明一个SQL语句
                        方式2：可以通过’’添加SQL语句
USING 子句             ---用于定义输入变量

例子

提前声明SQL的例子

CREATE OR REPLACE PROCEDURE p_5_1_1 AS
  sql1 VARCHAR (100);
BEGIN
  sql1 := 'UPDATE emp SET col=? where id=? ';     --提前声明SQL语句
  EXECUTE IMMEDIATE sql1 USING 'zhangsan-q',100;
END;
/

通过’’添加SQL语句例子

CREATE OR REPLACE PROCEDURE p_5_1_2 AS
  sql1 VARCHAR (100);
BEGIN
  EXECUTE IMMEDIATE 'UPDATE emp SET col=? where id=? ' USING 'zhangsan-q',100;
END;
/

使用USING子句的例子

DROP TABLE IF EXISTS emp2;
CREATE TABLE emp2(col1 VARCHAR(20),col2 INT);

CREATE or REPLACE PROCEDURE p_5_1_3(v_col1 varchar(20),v_col2 INT) AS
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO emp2 VALUES(?, ?)' USING v_col1, v_col2;
END;
/
CALL p_5_1_3 ( '2232',100);

使用动态SQL批量赋值,361版本该功能尚不完善。

drop table t1 cascade constraints;
create table t1(id int,c1 varchar2(20),c2 number(10,2));
insert into t1 values(1,'test1',1.1),(1,'test2',2.2);

create or replace procedure p1(v_id int)
is
type deftype1 is table of int;
tp1 deftype1;
v_sql varchar2(200);
begin
v_sql:='select id from t1 where id=:v_id';
execute immediate v_sql bulk collect into tp1 using v_id ;
for i in tp1.first .. tp1.last
loop
dbms_output.put_line(tp1(i));
end loop;
end;
/
过程已创建。

SQL> call p1(1);
1
1

调用完成。

使用游标语法

如果一个动态SQL语句是一个返回多行的SELECT语句，需要使用游标处理，提供了如下处理方法：

定义游标：使用sys_refcursor定义游标变量
打开游标：OPEN FOR语句把一个游标变量和动态SQL语句关联
使用游标：使用FETCH语句获取结果集
关闭游标：使用CLOSE语句关闭游标变量

例子：

CREATE OR REPLACE PROCEDURE p_5_2 IS
  cursor_a SYS_REFCURSOR;
  v_id INT;
  test_using INT:=2;
BEGIN
  OPEN cursor_a FOR 'SELECT * FROM t1 WHERE id = ?' USING test_using;
  LOOP
    FETCH cursor_a INTO v_id;
    EXIT WHEN  cursor_a%NOTFOUND;
    INSERT INTO t2 VALUES(v_id);
  END LOOP;
  CLOSE cursor_a;
END;
/

使用EXECUTE IMMEDIATE​

使用游标语法​

使用EXECUTE IMMEDIATE

使用游标语法